# database design
from datetime import datetime
import hashlib
from werkzeug.security import generate_password_hash, check_password_hash
from itsdangerous import TimedJSONWebSignatureSerializer as Serializer
from markdown import markdown
import bleach
from flask import current_app, request
from flask_login import UserMixin, AnonymousUserMixin
from flask import Flask
from . import db, login_manager
from flask_sqlalchemy import SQLAlchemy



class Openingconfig(db.Model):
    __tablename__ = "openingconfig"

    opening_id = db.Column(db.Integer, primary_key=True, unique=True)
    value = db.Column(db.Boolean, default=True)


class Professor(UserMixin, db.Model):
    __tablename__ = "professor"

    professor_id = db.Column(db.Integer, primary_key=True, unique=True)
    password_hash = db.Column(db.String(128))
    professor_name = db.Column(db.String(8))
    date_of_birth = db.Column(db.DateTime)
    social_security_number = db.Column(db.String(8))
    status = db.Column(db.String(8))
    department = db.Column(db.String(20))
    teaches = db.relationship("Teaches", backref="professor")

    def __init__(self, **kwargs):
        super(Professor, self).__init__(**kwargs)

    @property
    def password(self):
        raise AttributeError('password is not a readable attribute')

    @password.setter
    def password(self, password):
        self.password_hash = generate_password_hash(password)

    def verify_password(self, password):
        return check_password_hash(self.password_hash, password)

    def get_id(self):
        return self.professor_id

    def get_name(self):
        return self.professor_name

    def is_student(self):
        return False

    def is_professor(self):
        return True

    def is_registrar(self):
        return False


class Student(UserMixin, db.Model):
    __tablename__ = "student"

    student_id = db.Column(db.Integer, primary_key=True, unique=True)
    password_hash = db.Column(db.String(128))
    student_name = db.Column(db.String(8))
    date_of_birth = db.Column(db.DateTime)
    social_security_number = db.Column(db.String(8))
    status = db.Column(db.String(8))
    graduation_date = db.Column(db.DateTime)
    department = db.Column(db.String(20))
    balance = db.Column(db.Float)
    takes = db.relationship("Takes", backref="student")
    bill = db.relationship("Bill", backref="user")
    inputbox = db.relationship("Inputbox", backref="user")

    def __init__(self, **kwargs):
        super(Student, self).__init__(**kwargs)

    @property
    def password(self):
        raise AttributeError('password is not a readable attribute')

    @password.setter
    def password(self, password):
        self.password_hash = generate_password_hash(password)

    def get_id(self):
        return self.student_id

    def get_name(self):
        return self.student_name

    def is_student(self):
        return True

    def is_professor(self):
        return False

    def is_registrar(self):
        return False

    def verify_password(self, password):
        return check_password_hash(self.password_hash, password)


class Registrar(UserMixin, db.Model):
    __tablename__ = "registrar"

    registrar_id = db.Column(db.Integer, primary_key=True, unique=True)
    password_hash = db.Column(db.String(128))
    registrar_name = db.Column(db.String(8))
    date_of_birth = db.Column(db.DateTime)
    social_security_number = db.Column(db.String(8))
    status = db.Column(db.String(8))

    def __init__(self, **kwargs):
        super(Registrar, self).__init__(**kwargs)

    @property
    def password(self):
        raise AttributeError('password is not a readable attribute')

    @password.setter
    def password(self, password):
        self.password_hash = generate_password_hash(password)

    def verify_password(self, password):
        return check_password_hash(self.password_hash, password)

    def get_id(self):
        return self.registrar_id

    def get_name(self):
        return self.registrar_name

    def is_student(self):
        return False

    def is_professor(self):
        return False

    def is_registrar(self):
        return True


class Course(db.Model):
    __tablename__ = "course"

    course_id = db.Column(db.Integer, primary_key=True, unique=True)
    course_name = db.Column(db.String(8))
    department = db.Column(db.String(8))
    description = db.Column(db.Text)
    credit = db.Column(db.Float)
    price = db.Column(db.Float)
    # 用来标记学期，用5位数字表示，如20191表示2019年第一学期，一年只有两个学期
    # 这里是按照时间分配学期，例如，现在是2020年5月，记录为2020第一学期，并非传统的2019-2020第二学期
    taught = db.relationship("Teaches", backref="course")
    taken = db.relationship("Takes", backref="course")
    section = db.relationship("Section", backref="course")
    bill = db.relationship("Bill", backref="course")

    # pre_course = db.relationship("Prerequisite",backref="pre_course")
    # course = db.relationship("Prerequisite",backref="course")

    def __init__(self, **kwargs):
        super(Course, self).__init__(**kwargs)
        self.price = 100 * self.credit


class Bill(db.Model):
    __tablename__ = "bill"

    bill_id = db.Column(db.Integer, primary_key=True, unique=True)
    student_id = db.Column(db.Integer, db.ForeignKey('student.student_id'))
    course_id = db.Column(db.Integer, db.ForeignKey('course.course_id'))
    course_price = db.Column(db.Float)
    hasBeenPaid = db.Column(db.Boolean, default=False)


class Prerequisite(db.Model):
    __tablename__ = "prerequisite"

    id = db.Column(db.Integer, primary_key=True, unique=True)
    pre_id = db.Column(db.Integer, db.ForeignKey('course.course_id'))
    course_id = db.Column(db.Integer, db.ForeignKey('course.course_id'))
    course = db.relationship('Course', foreign_keys=course_id)
    pre_course = db.relationship('Course', foreign_keys=pre_id)


class Teaches(db.Model):
    __tablename__ = "teaches"

    teaches_id = db.Column(db.Integer, primary_key=True, unique=True)
    professor_id = db.Column(db.Integer, db.ForeignKey("professor.professor_id", ondelete='CASCADE'))
    course_id = db.Column(db.Integer, db.ForeignKey("course.course_id", ondelete='CASCADE'))
    section_id = db.Column(db.Integer, db.ForeignKey('section.section_id', ondelete='CASCADE'))


class Takes(db.Model):
    __tablename__ = "takes"

    takes_id = db.Column(db.Integer, primary_key=True, unique=True)  # Takes表中不能少主键，增删改查需要用

    section_id = db.Column(db.Integer, db.ForeignKey("section.section_id", ondelete='CASCADE'))
    student_id = db.Column(db.Integer, db.ForeignKey("student.student_id", ondelete='CASCADE'))
    course_id = db.Column(db.Integer, db.ForeignKey("course.course_id", ondelete='CASCADE'))
    isPrimary = db.Column(db.Boolean, default=True)
    grade = db.Column(db.String(1))


class Inputbox(db.Model):
    __tablename__ = "inputbox"

    inputbox_id = db.Column(db.Integer, primary_key=True, unique=True)
    user_id = db.Column(db.Integer, db.ForeignKey('student.student_id'))
    message = db.Column(db.Text)
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    hasBeenRead = db.Column(db.Boolean, default=False)


class Section(db.Model):
    __tablename__ = "section"

    section_id = db.Column(db.Integer, primary_key=True, unique=True)
    course_id = db.Column(db.Integer, db.ForeignKey("course.course_id"))
    semester = db.Column(db.String(5))
    teaches = db.relationship("Teaches", backref="section")
    takes = db.relationship("Takes", backref="section")


class AnonymousUser(AnonymousUserMixin):
    def can(self):
        return False


login_manager.anonymous_user = AnonymousUser


@login_manager.user_loader
def load_user(id):
    student = Student.query.filter(Student.student_id == id).first()
    professor = Professor.query.filter(Professor.professor_id == id).first()
    registrar = Registrar.query.filter(Registrar.registrar_id == id).first()
    if student is not None:
        return student

    if professor is not None:
        return professor

    if registrar is not None:
        return registrar
# if __name__ == "__main__":
#     db.drop_all()
#     db.create_all()
#
#     date_of_birth = datetime(1997, 12, 29)
#     graduation_date = datetime(2021, 6, 1)
#     stu = Student(student_id=21172306, student_name="张权", date_of_birth=date_of_birth,
#                   social_security_number="00000000",
#                   status="学生", graduation_date=graduation_date, department="计算机科学与技术学院", balance=1000)
#     db.session.add(stu)
#     db.session.commit()
#
#     pro = Professor(professor_id=51172306, professor_name="吕帅", date_of_birth=date_of_birth,
#                     social_security_number="00000000",
#                     status="教授", department="计算机科学与技术学院")
#     db.session.add(pro)
#     db.session.commit()
#
#     registrar = Registrar(registrar_id=11111111, registrar_name="李元元", date_of_birth=date_of_birth,
#                           social_security_number="00000000", status="校长")
#     db.session.add(registrar)
#     db.session.commit()
#
#     course = Course(course_id=12345678, course_name="数据结构", department="计算机科学与技术", description="数据结构实现", credit=5.5)
#     db.session.add(course)
#     db.session.commit()
#
#     course = Course(course_id=11111111, course_name="数据挖掘", department="计算机科学与技术", description="数据挖掘算法", credit=2)
#     db.session.add(course)
#     db.session.commit()
#
#     """
#     bill_id = db.Column(db.Integer, primary_key=True, unique=True)
#     student_id = db.Column(db.Integer, db.ForeignKey('student.student_id'))
#     course_id = db.Column(db.Integer, db.ForeignKey('course.course_id'))
#     course_price = db.Column(db.Float)
#     hasBeenPaid = db.Column(db.Boolean, default=False)
#     """
#     bill1 = Bill(bill_id=12345677, student_id=21172306, course_id=11111111, course_price=200, hasBeenPaid=False)
#     bill2 = Bill(bill_id=22222222, student_id=21172306, course_id=12345678, course_price=200, hasBeenPaid=False)
#     db.session.add(bill1)
#     db.session.add(bill2)
#     db.session.commit()
#     bill = Bill.query.filter(Bill.student_id == 21172306).all()
#     price = [b.course_price for b in bill]
#     # print(sum(price))
#     """
#     __tablename__ = "prequisite"
#
#     id = db.Column(db.Integer, primary_key=True, unique=True)
#     pre_id = db.Column(db.Integer, db.ForeignKey('course.course_id'))
#     course_id = db.Column(db.Integer, db.ForeignKey('course.course_id'))
#     course = db.relationship('Course', foreign_keys=course_id)
#     pre_course = db.relationship('Course', foreign_keys=pre_id)
#     """
#     prerequisite = Prerequisite(id=1, pre_id=12345678, course_id=11111111)
#     db.session.add(prerequisite)
#     db.session.commit()
#     # print(prerequisite.course,prerequisite.pre_course)
#     """
#     section_id = db.Column(db.Integer, primary_key=True, unique=True)
#     course_id = db.Column(db.Integer,db.ForeignKey("course.course_id"))
#     semester = db.Column(db.String(5))
#     teaches = db.relationship("Teaches",backref="section")
#     takes = db.relationship("Takes",backref="section")
#     """
#     section = Section(section_id=1, course_id=11111111, semester="20201")
#     db.session.add(section)
#     db.session.commit()
#     s = Section.query.filter(Section.section_id == 1).filter(Section.course_id == 11111111).first()
#     # print(s.section_id,s.course_id)
#     # print(section.course)
#     """
#     teaches_id = db.Column(db.Integer, primary_key=True, unique=True)
#     professor_id = db.Column(db.Integer, db.ForeignKey("professor.professor_id"))
#     course_id = db.Column(db.Integer, db.ForeignKey("course.course_id"))
#     section_id = db.Column(db.Integer, db.ForeignKey('section.section_id'))
#     """
#     teaches = Teaches(teaches_id=1, professor_id=51172306, course_id=11111111, section_id=1)
#     db.session.add(teaches)
#     db.session.commit()
#     # print(teaches.section,teaches.course,teaches.professor)
#
#     """
#     takes_id = db.Column(db.Integer, primary_key=True, unique=True)  # Takes表中不能少主键，增删改查需要用
#
#     section_id = db.Column(db.Integer, db.ForeignKey("section.section_id"))
#     student_id = db.Column(db.Integer, db.ForeignKey("student.student_id"))
#     course_id = db.Column(db.Integer, db.ForeignKey("course.course_id"))
#     isPrimary = db.Column(db.Boolean, default=True)
#     grade = db.Column(db.Float)
#     """
#     takes = Takes(takes_id=1, section_id=1, student_id=21172306, course_id=11111111)
#     db.session.add(takes)
#     db.session.commit()
#     # print(takes.course,takes.student,takes.section)
#     """
#     user_id = db.Column(db.Integer, db.ForeignKey('student.student_id'),primary_key=True,unique=True)
#     message = db.Column(db.Text)
#     timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
#     hasBeenRead = db.Column(db.Boolean, default=False)
#     """
#     timestamp = datetime.now()
#     inputbox = Inputbox(user_id=21172306, message="你好", timestamp=timestamp, hasBeenRead=False)
#     db.session.add(inputbox)
#     db.session.commit()
#     # print(inputbox.user)
